<?php

namespace console\controllers;

use common\models\BaseGoodsSite;
use common\models\BaseLogisticsInterfaceLog;
use common\models\BaseSupplierGoods;
use common\models\LogisticsShOrderLog;
use common\models\LogisticsShOrderStatusLog;
use common\models\SendErrorMessage;
use yii\console\Controller;
use common\models\BaseSite;
use common\models\SysUser;
use common\models\BaseSaleMan;
use common\models\BaseGrid;
use common\models\BaseRegion;
use yii\db\Query;
use common\models\GoodsComment;
use common\models\AfterSalesProcessTime;
use common\models\BaseBookingGoods;
use common\models\LogisticsSerialCodeLog;
use common\models\LogisticsShOrderEditLog;
use common\models\AfterSalesOrders;

/**
 * 计划任务，下载数据报表
 * 
 * yii export
 * 
 * yii 
 * 
 * @author kai.gao
 *
 */
class ExportController extends Controller{
	
	public function init() {
		parent::init();
		set_time_limit(0);
		ini_set('memory_limit', '1024M');
	}
	
	/**
	 * @Title: actionExportAll
	 * @Description: 导出所有客户
	 * @return: return_type
	 * @author: weilong.yin
	 * @date: 2015-1-15 下午8:40:39
	 */
	public function actionExportAll(){
		
		$query = (new Query())->select('t1.id,t1.login_account,t1.user_name,t1.shop_name,t1.email,t2.region_name as province,t3.region_name as city,t4.region_name as district,t1.address,t1.phone,t1.tel,t1.qq,
										t1.last_ip,t1.reg_time,t1.last_login_time,t1.sex,t1.integral,t1.level,t1.user_status,t5.site_name,t6.salesman_name,t7.grid_name,t1.denied_num')
							  ->from("{{%sys_user}} as t1")
							  ->leftJoin("{{%base_region}} as t2","t1.province=t2.region_id")
							  ->leftjoin("{{%base_region}} as t3","t1.city=t3.region_id")
							  ->leftJoin("{{%base_region}} as t4","t1.district=t4.region_id")
							  ->leftJoin("{{%base_site}} as t5","t1.site_id=t5.id")
							  ->leftJoin("{{%base_sale_man}} as t6","t1.salesman_id=t6.id")
							  ->leftJoin("{{%base_grid}} as t7","t1.grid_id=t7.id");
		
	
		$limit = 1000; //防止内存溢出，每次加载1000条
	
		/* 实例化类 */
		$objPHPExcel = new \PHPExcel();
		/* 设置输出的excel文件为2007兼容格式 */
		$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
		/* 设置当前的sheet */
		$objPHPExcel->setActiveSheetIndex(0);
		$objActSheet = $objPHPExcel->getActiveSheet();
		$objActSheet->setTitle('全站所有会员信息表');
	
		$objActSheet->setCellValue('A1','编号');
		$objActSheet->setCellValue('B1','登录帐号');
		$objActSheet->setCellValue('C1','用户名');
		$objActSheet->setCellValue('D1','店铺名称');
		$objActSheet->setCellValue('E1','邮箱');
		$objActSheet->setCellValue('F1','省份');
		$objActSheet->setCellValue('G1','城市');
		$objActSheet->setCellValue('H1','区/县');
		$objActSheet->setCellValue('I1','详细地址');
		$objActSheet->setCellValue('J1','手机号码');
		$objActSheet->setCellValue('K1','电话');
		$objActSheet->setCellValue('L1','QQ');
		$objActSheet->setCellValue('M1','最后登录IP');
		$objActSheet->setCellValue('N1','注册时间');
		$objActSheet->setCellValue('O1','最后登录时间');
		$objActSheet->setCellValue('P1','性别');
		$objActSheet->setCellValue('Q1','积分');
		$objActSheet->setCellValue('R1','等级');
		$objActSheet->setCellValue('S1','用户状态');
		$objActSheet->setCellValue('T1','站点');
		$objActSheet->setCellValue('U1','业务员');
		$objActSheet->setCellValue('V1','网格');
		$objActSheet->setCellValue('W1','拒签次数');
	
		$i =2;
		foreach ($query->each($limit) as $value) {
			/* excel文件内容 */
			$objActSheet->setCellValue('A'.$i,$value['id']);
			$objActSheet->setCellValue('B'.$i,$value['login_account']);
			$objActSheet->setCellValue('C'.$i,$value['user_name']);
			$objActSheet->setCellValue('D'.$i,$value['shop_name']);
			$objActSheet->setCellValue('E'.$i,$value['email']);
			$objActSheet->setCellValue('F'.$i,$value['province']);
			$objActSheet->setCellValue('G'.$i,$value['city']);
			$objActSheet->setCellValue('H'.$i,$value['district']);
			$objActSheet->setCellValue('I'.$i,$value['address']);
			$objActSheet->setCellValue('J'.$i,$value['phone']);
			$objActSheet->setCellValue('K'.$i,$value['tel']);
			$objActSheet->setCellValue('L'.$i,$value['qq']);
			$objActSheet->setCellValue('M'.$i,$value['last_ip']);
			$objActSheet->setCellValue('N'.$i,date('Y-m-d H:i:s',$value['reg_time']));
			$objActSheet->setCellValue('O'.$i,date('Y-m-d H:i:s',$value['last_login_time']));
			$objActSheet->setCellValue('P'.$i,SysUser::getSex($value['sex']));
			$objActSheet->setCellValue('Q'.$i,$value['integral']);
			$objActSheet->setCellValue('R'.$i,$value['level']);
			$objActSheet->setCellValue('S'.$i,SysUser::getUserStatus($value['user_status']));
			$objActSheet->setCellValue('T'.$i,$value['site_name']);
			$objActSheet->setCellValue('U'.$i,$value['salesman_name']);
			$objActSheet->setCellValue('V'.$i,$value['grid_name']);
			$objActSheet->setCellValue('W'.$i,$value['denied_num']);
			$i++;
		}
	
		/* 生成到浏览器，提供下载 */
		$filename= '0-member-table.xls';
		$objWriter->save(\Yii::$app->basePath.'/../download/'.$filename);
	
	}
	
	/**
	 * @Title: actionExportBySite
	 * @Description: 根据站点导出多张客户报表
	 * @return: return_type
	 * @author: weilong.yin
	 * @date: 2015-1-15 下午8:41:19
	 */
	public function actionExportBySite(){
	
		$site_data = BaseSite::find()->select('id,site_name')->asArray()->all();
	
		foreach ($site_data as $val){
						
			$query = (new Query())->select('t1.id,t1.login_account,t1.user_name,t1.shop_name,t1.email,t2.region_name as province,t3.region_name as city,t4.region_name as district,t1.address,t1.phone,t1.tel,t1.qq,
										t1.last_ip,t1.reg_time,t1.last_login_time,t1.sex,t1.integral,t1.level,t1.user_status,t5.site_name,t6.salesman_name,t7.grid_name,t1.denied_num')
													->from("{{%sys_user}} as t1")
													->leftJoin("{{%base_region}} as t2","t1.province=t2.region_id")
													->leftjoin("{{%base_region}} as t3","t1.city=t3.region_id")
													->leftJoin("{{%base_region}} as t4","t1.district=t4.region_id")
													->leftJoin("{{%base_site}} as t5","t1.site_id=t5.id")
													->leftJoin("{{%base_sale_man}} as t6","t1.salesman_id=t6.id")
													->leftJoin("{{%base_grid}} as t7","t1.grid_id=t7.id")
													->where(['site_id'=>$val['id']]);
				
			$limit = 1000; //防止内存溢出，每次加载1000条
			/* 实例化类 */
			$objPHPExcel = new \PHPExcel();
			/* 设置输出的excel文件为2007兼容格式 */
			$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
			/* 设置当前的sheet */
			$objPHPExcel->setActiveSheetIndex(0);
			$objActSheet = $objPHPExcel->getActiveSheet();
			$objActSheet->setTitle($val['site_name'].'会员信息表');
				
			$objActSheet->setCellValue('A1','编号');
			$objActSheet->setCellValue('B1','登录帐号');
			$objActSheet->setCellValue('C1','用户名');
			$objActSheet->setCellValue('D1','店铺名称');
			$objActSheet->setCellValue('E1','邮箱');
			$objActSheet->setCellValue('F1','省份');
			$objActSheet->setCellValue('G1','城市');
			$objActSheet->setCellValue('H1','区/县');
			$objActSheet->setCellValue('I1','详细地址');
			$objActSheet->setCellValue('J1','手机号码');
			$objActSheet->setCellValue('K1','电话');
			$objActSheet->setCellValue('L1','QQ');
			$objActSheet->setCellValue('M1','最后登录IP');
			$objActSheet->setCellValue('N1','注册时间');
			$objActSheet->setCellValue('O1','最后登录时间');
			$objActSheet->setCellValue('P1','性别');
			$objActSheet->setCellValue('Q1','积分');
			$objActSheet->setCellValue('R1','等级');
			$objActSheet->setCellValue('S1','用户状态');
			$objActSheet->setCellValue('T1','站点');
			$objActSheet->setCellValue('U1','业务员');
			$objActSheet->setCellValue('V1','网格');
			$objActSheet->setCellValue('W1','拒签次数');
				
			$i =2;
			foreach ($query->each($limit) as $value) {
				/* excel文件内容 */
				$objActSheet->setCellValue('A'.$i,$value['id']);
				$objActSheet->setCellValue('B'.$i,$value['login_account']);
				$objActSheet->setCellValue('C'.$i,$value['user_name']);
				$objActSheet->setCellValue('D'.$i,$value['shop_name']);
				$objActSheet->setCellValue('E'.$i,$value['email']);
				$objActSheet->setCellValue('F'.$i,$value['province']);
				$objActSheet->setCellValue('G'.$i,$value['city']);
				$objActSheet->setCellValue('H'.$i,$value['district']);
				$objActSheet->setCellValue('I'.$i,$value['address']);
				$objActSheet->setCellValue('J'.$i,$value['phone']);
				$objActSheet->setCellValue('K'.$i,$value['tel']);
				$objActSheet->setCellValue('L'.$i,$value['qq']);
				$objActSheet->setCellValue('M'.$i,$value['last_ip']);
				$objActSheet->setCellValue('N'.$i,date('Y-m-d H:i:s',$value['reg_time']));
				$objActSheet->setCellValue('O'.$i,date('Y-m-d H:i:s',$value['last_login_time']));
				$objActSheet->setCellValue('P'.$i,SysUser::getSex($value['sex']));
				$objActSheet->setCellValue('Q'.$i,$value['integral']);
				$objActSheet->setCellValue('R'.$i,$value['level']);
				$objActSheet->setCellValue('S'.$i,SysUser::getUserStatus($value['user_status']));
				$objActSheet->setCellValue('T'.$i,$value['site_name']);
				$objActSheet->setCellValue('U'.$i,$value['salesman_name']);
				$objActSheet->setCellValue('V'.$i,$value['grid_name']);
				$objActSheet->setCellValue('W'.$i,$value['denied_num']);
				$i++;
			}
	
			//ob_end_clean();  //清空缓存
			$filename= $val['id'].'-member-table.xls';
			$objWriter->save(\Yii::$app->basePath.'/../download/'.$filename);
	
		}
	}            
        
        //自动审核
        public function actionChangeorder_status(){
            \common\models\OmsOrderInfo::orderVerify();
            
        }
        
        //红包过期提醒
        public function actionRedBagRemind(){
        	\common\models\SysRedBagUser::redBagRemind();
        }
        
       
        /**
         * @description: 订单推送给物流
         * @return: return_type
         * @author: leo
         * @date: 2015年6月29日上午4:42:53
         * @modified_date: 2015年6月29日上午4:42:53
         * @modified_user: leo
         * @review_user:
        */
        public function actionSendOrderList(){
		  echo file_get_contents('http://www.51dh.com.cn/interface/crontab-list');
        
        }
        
        /**
         * @Title: actionGetOrderStatus
         * @Description: 整合订单json数据加入到临时表
         * @return: return_type
         * @author: weilong.yin
         * @date: 2015-6-4 下午8:33:35
         */
        public function actionPushToExpress(){
           echo file_get_contents('http://ms.51dh.com.cn/oms-to-express-details/again-to-express');    
        }

		/**
		 * @Title: actionBatchToZero
		 * @Description: 批量修改推送次数
		 * @return: return_type
		 * @author: weilong.yin
		 * @date: 2015年8月27日 18:58:35
		 */
		public function actionBatchToZero(){
			BaseLogisticsInterfaceLog::updateLogisticsOrderLog();
			BaseLogisticsInterfaceLog::updateLogisticsOrderEditLog();
			BaseLogisticsInterfaceLog::updateLogisticsOrderStatusLog();
			BaseLogisticsInterfaceLog::updateLogisticsExpressCodeLog();
			BaseLogisticsInterfaceLog::checkOrderSave();
			BaseLogisticsInterfaceLog::getOrderSaveProductNum();
			BaseLogisticsInterfaceLog::updateLogisticsShOrder();
			BaseLogisticsInterfaceLog::updateLogisticsShOrderStatus();
			BaseLogisticsInterfaceLog::updateLogisticsSerialCodeLog();
			BaseLogisticsInterfaceLog::updateLogisticsOrderEditLog();
		}
        
		/**
		 * @description:添加串码
		 * @return: return_type
		 * @author: leo
		 * @date: 2015年9月23日上午11:56:16
		 * @modified_date: 2015年9月23日上午11:56:16
		 * @modified_user: leo
		 * @review_user:
		*/
		public function actionAddSerialCodeList(){
		    LogisticsSerialCodeLog::addSerialCodeList();
		}
        /**
         * @description:订单修改推送
         * @return: return_type
         * @author: leo
         * @date: 2015年6月29日上午4:29:46
         * @modified_date: 2015年6月29日上午4:29:46
         * @modified_user: leo
         * @review_user:
        */
        public function actionAddOrderEditList(){
            \common\models\LogisticsSynchronizationOrderLog::addOrderEditList();
        }
        
        
        /**
         * @description:订单状态推送
         * @return: return_type
         * @author: leo
         * @date: 2015年6月29日上午4:30:52
         * @modified_date: 2015年6月29日上午4:30:52
         * @modified_user: leo
         * @review_user:
        */
        public function actionAddOrderStatusList(){
            \common\models\LogisticsSynchronizationOrderStatusLog::addOrderStatusList();
        }
        
        /**
         * @description:推送运单号
         * @return: return_type
         * @author: leo
         * @date: 2015年6月29日上午4:31:33
         * @modified_date: 2015年6月29日上午4:31:33
         * @modified_user: leo
         * @review_user:
        */
        public function actionAddExpressCodeList(){
            \common\models\LogisticsSynchronizationExpressCodeLog::addExpressCodeList();
        }
  
        
        //服务费计算
        public function actionChargeService(){
        	\common\models\OmsOrderGoods::GetServiceCharge();
        }
        
        // 更新供应商商品仓库信息
        public function actionUpdateSuppGoodsDepotInfo(){
        	\common\models\NewOldSupplier::updateSupplierInfo();
        }
        
        //财富值初始数据
        public function actionUserExpValue(){
        	\common\models\SysUser::initialExpValue();
        }
        
        //消耗财富值更新等级
        public function actionUpdateLevel(){
        	\common\models\SysUser::expendExpValue();
        }
       
        /**
         * @description:自动评论
         * @return: return_type
         * @author: leo
         * @date: 2015年7月6日下午6:11:38
         * @modified_date: 2015年7月6日下午6:11:38
         * @modified_user: leo
         * @review_user:
        */
        public function actionAutoComment(){
            GoodsComment::autoComment();
        }

        /**
         * @description:每小时执行各个站点商品报价
         * @return: return_type
         * @author: leo
         * @date: 2015年7月10日 17:29:15
         * @modified_date: 2015年7月10日 17:29:19
         * @modified_user: leo
         * @review_user:
         */
        public function actionDownSiteGoodsPrice(){
                BaseSupplierGoods::SiteLowPrice();
        }

        /**
         * @description:统计sku
         * @return: return_type
         * @author: leo
         * @date: 2015年7月15日上午11:44:47
         * @modified_date: 2015年7月15日上午11:44:47
         * @modified_user: leo
         * @review_user:
        */
        public function actionDownSku(){
            BaseSupplierGoods::getSku();
        }
         /**
         * @description:统计指定品牌品类sku
         * @return: return_type
         * @author: zendwang
         * @date: 2015年8月19日上午09:18
         * @modified_date: 2015年8月19日上午9:21:54
         * @modified_user: zendwang
         * @review_user:
        */
        public function actionDownBrandTypeSku(){
            BaseSupplierGoods::getBrandTypeSku();
        }
        /**
        * @description: 查找供应商3天未修改价格的商品，并给相应的供应商发站内性
         * @author: qiang.zuo
         * @date: 2015-7-16 11:00:57
        */
        public function actionNoModify(){
            BaseSupplierGoods::getNoModifySupplier();
        }
        
        /**
         * @description:售后红包自动发放
         * @return: return_type
         * @author: leo
         * @date: 2015年8月18日下午3:13:19
         * @modified_date: 2015年8月18日下午3:13:19
         * @modified_user: leo
         * @review_user:
        */
        public function actionSendRedBag(){
            AfterSalesProcessTime::SendRedBags();
        }
        
        /**
         * @desc 新列表页初始数据
         * @author jiangtao.ren
         */
        public function actionUpdateBaseInfo(){
        	BaseGoodsSite::updateBaseData();
        }
		/**
		 * @desc 售后单推送
		 * @author leo
		 */
		public function actionAddShOrder(){
			LogisticsShOrderLog::addShOrderList();
		}

		/**
		 * @desc 售后单状态推送
		 * @author leo
		 */
		public function actionAddShOrderStatus(){
			LogisticsShOrderStatusLog::addShOrderStatusList();
		}

		/**
		 * @desc 短信提醒
		 * @author leo
		 */
		public function actionSendMessage(){
				SendErrorMessage::sendMesToCustomerServices();
		}
		
		/**
		 * @desc 商品预约
		 * @author jiangtao.ren
		 */
		public function actionBookingGoods(){
			BaseBookingGoods::updateGoodsInfo(41);
		}

        /**
		 * @desc 51快换自动审核
		 * @author hongyuan.zhang
		 */
		public function actionAfterSaleCheck(){
			\common\models\AfterSalesOrders::autoCheckOrders();
		}
		
		/**
		 * @description:售后单修改推送物流
		 * @return: return_type
		 * @author: leo
		 * @date: 2015年10月8日上午10:04:01
		 * @modified_date: 2015年10月8日上午10:04:01
		 * @modified_user: leo
		 * @review_user:
		*/
		public function actionShOrderEdit(){
            LogisticsShOrderEditLog::addShOrderEdit();
		}
		
		/**
		 * @Title: actionSecondVeriy
		 * @Description: 第二品类自动审核
		 * @return: return_type
		 * @author: weilong.yin
		 * @date: 2015年10月27日上午11:05:24
		*/
		public function actionSecondVeriy(){
		    AfterSalesOrders::AutoSecond();
		}
		/**
		 * @Title: actionGetAllowancePrice
		 * @Description: 获取补贴供应商最低价
		 * @return: return_type
		 * @author: jian.zhang
		 * @date: 2015年11月2日上午10:40:24
		*/
		public function actionGetAllowancePrice(){
		    SupplierAllowanceLog::GetLowestPrice();
		}

        /**
         * @description:批量恢复用户诚信点
         * @return: return_type
         * @author: hongyuan.zhang
         * @date: 2015年11月2日14:01:33
         */
        public function actionEditUserPoint(){
            ini_set ('memory_limit', '1024M');
            set_time_limit(0);
            //分等级增加诚信点
            $connection = \Yii::$app->db;
            $command = $connection->createCommand('UPDATE dh_sys_user SET integrity_point = 2 WHERE level = 1');
            $command->execute();
            $command = $connection->createCommand('UPDATE dh_sys_user SET integrity_point = 4 WHERE level = 2');
            $command->execute();
            $command = $connection->createCommand('UPDATE dh_sys_user SET integrity_point = 6 WHERE level = 3');
            $command->execute();
            $command = $connection->createCommand('UPDATE dh_sys_user SET integrity_point = 8 WHERE level = 4');
            $command->execute();

            // $m = date('Y-m-d', mktime(0,0,0,date('m')-1,1,date('Y'))); //上个月的开始日期
            // $t = date('t',strtotime($m)); //上个月共多少天
            // $start = mktime(0,0,0,date('m')-1,1,date('Y')); //上个月的开始日期
            // $end = mktime(23,59,59,date('m')-1,$t,date('Y')); //上个月的结束日期

            // //上月惩罚记录  无处罚记录增加财富值

            // $res = SysUser::find()->select('id')->column();
            // foreach ($res as $val){
            //     $list = UserPunishInfo::find()->where(['user_id'=>$val])->andWhere(['between','time',$start,$end])->one();
            //     if (!$list){
            //         $model = SysUser::find()->select('exp_value')->where(['id'=>$val])->one();
            //         $model -> exp_value = $model->exp_value+10;
            //         $model -> save();
            //     }
            // }
        }
                
}

?>